﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Common.Entities;

namespace Common.Models
{
    public class OrderRepository
    {
        public List<Order> GetOrdersByVisit(int idVisit)
        {
            try
            {
                using (SqlCeConnection connection = WorkDataBase.CreateConnection())
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    string sqlQuery = "SELECT IdOrder, IsExport, DateOrder, SumOrder, NumberOrder, VisitId" +
                                      " FROM Orders" +
                                      " Where VisitId = @VisitId";
                    using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                    {
                        com.Parameters.Add("@VisitId", idVisit);
                        using (SqlCeDataReader dr = com.ExecuteReader())
                        {
                            List<Order> orders = new List<Order>(5);
                            Order order;
                            int idOrder = dr.GetOrdinal("IdOrder");
                            int isReadOnly = dr.GetOrdinal("IsExport");
                            int dateOrder = dr.GetOrdinal("DateOrder");
                            int sumOrder = dr.GetOrdinal("SumOrder");
                            int numberOrder = dr.GetOrdinal("NumberOrder");
                            int visitId = dr.GetOrdinal("VisitId");
                            while (dr.Read())
                            {
                                order = new Order();
                                order.IdOrder = dr.GetInt32(idOrder);
                                order.IsReadOnly = dr.GetBoolean(isReadOnly);
                                order.DateOrder = dr.GetDateTime(dateOrder);
                                order.SumOrder = dr.GetDouble(sumOrder);
                                order.NumberOrder = dr.GetString(numberOrder);
                                order.VisiId = dr.GetInt32(visitId);
                                order.RecordOrders =
                                    DataManager.GetInstance().OrderRecords.GetOrderRecordByOrder(order.IdOrder);
                                order.SumOrder = order.RecordOrders.Sum(r => r.Amount);
                                orders.Add(order);
                                
                            }
                            return orders;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Order GetOrderById (int orderId)
        {
            try
            {
                using (SqlCeConnection connection = WorkDataBase.CreateConnection())
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    string sqlQuery = "SELECT IdOrder, IsExport, DateOrder, SumOrder, NumberOrder, VisitId" +
                                      " FROM Orders" +
                                      " Where IdOrder = @IdOrder";
                    using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                    {
                        com.Parameters.Add("@IdOrder", orderId);
                        using (SqlCeDataReader dr = com.ExecuteReader())
                        {

                            int idOrder = dr.GetOrdinal("IdOrder");
                            int isReadOnly = dr.GetOrdinal("IsExport");
                            int dateOrder = dr.GetOrdinal("DateOrder");
                            int sumOrder = dr.GetOrdinal("SumOrder");
                            int numberOrder = dr.GetOrdinal("NumberOrder");
                            int visitId = dr.GetOrdinal("VisitId");
                            if (dr.Read())
                            {
                                Order order;
                                order = new Order();
                                order.IdOrder = dr.GetInt32(idOrder);
                                order.IsReadOnly = dr.GetBoolean(isReadOnly);
                                order.DateOrder = dr.GetDateTime(dateOrder);
                                order.SumOrder = dr.GetDouble(sumOrder);
                                order.NumberOrder = dr.GetString(numberOrder);
                                order.VisiId = dr.GetInt32(visitId);
                                order.RecordOrders =
                                    DataManager.GetInstance().OrderRecords.GetOrderRecordByOrder(order.IdOrder);
                                order.SumOrder = order.RecordOrders.Sum(r => r.Amount);
                                return order;
                            }
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Order CreateOrder(int idVisit)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "insert into Orders (VisitId, IsExport, DateOrder, NumberOrder, SumOrder) values (@VisitId, @IsExport, @DateOrder, @NumberOrder, @SumOrder)";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@VisitId", idVisit);
                    com.Parameters.Add("@IsExport", false);
                    com.Parameters.Add("@DateOrder", DateTime.Now.Date);
                    com.Parameters.Add("@NumberOrder", "");
                    com.Parameters.Add("@SumOrder", SqlDbType.Float).Value = 0.00f;
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new Exception();
                    }
                }
            }
            List<Order> orders = GetOrdersByVisit(idVisit);
            Order order = orders.Single(o => o.IdOrder == (orders.Max(or => or.IdOrder)));
            return order;
        }

        public void DeleteOrder (int idOrder)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery = "delete from Orders where IdOrder = @IdOrder";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@IdOrder", idOrder);
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new Exception();
                    }
                }
            }
        }
    
        public void UpdateOrder (Order order)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "update orders set "+
                        "NumberOrder = @NumberOrder, DateOrder = @DateOrder, IsExport = @IsExport, SumOrder = @SumOrder " +
                        "where idOrder = @IdOrder";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@IdOrder", order.IdOrder);
                    com.Parameters.Add("@NumberOrder", order.NumberOrder);
                    com.Parameters.Add("@DateOrder", order.DateOrder);
                    com.Parameters.Add("@SumOrder", order.SumOrder);
                    com.Parameters.Add("@IsExport", order.IsReadOnly);
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new Exception();
                    }
                }
            }
        }
    }
    
}
